package Tools;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

public class jdbcTools {
    private static Connection connection=null;

//    private static String user;
//    private static String password;
//    private static String url;
//    private static String driver;

//    static {
//        //I/O
//        InputStream resourceAsStream = jdbcTools.class.getClassLoader().getResourceAsStream("JuTiFengZhuang/jdbc.properties");
//        Properties properties = new Properties();
//        try {
//            properties.load(resourceAsStream);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        user = properties.getProperty("user");
//        password = properties.getProperty("password");
//        url = properties.getProperty("url");
//        driver = properties.getProperty("driver");
//
////加载驱动
//        try {
//            Class.forName(driver);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
//        //释放资源
//        if (resourceAsStream != null) {
//            try {
//                resourceAsStream.close();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//    }

    /**
     * 1.完成数据库链接操作
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/student.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        connection=dataSource.getConnection();
        return connection;
//        Connection connection = DriverManager.getConnection(url, user, password);
//        return connection;
    }

    /**
     * 2.获取完整的预编译好的sql对象
     * @param sql
     * @param connection
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
        //1.获取元数据
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        //2.获取字符串需要的参数个数
        int count = parameterMetaData.getParameterCount();
        //3.判断是否传参，并且设置参数值
        if (count != 0 && parameters != null && parameters.length == count) {
            for (int i = 0; i < count; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
        }
        return statement;
    }

    /**
     * 3.实现更新
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int update1(String sql, Object... parameters) throws Exception {
        Connection connection = jdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 4.关闭对象，释放资源
     * @param resources
     */
    public static void closeall(AutoCloseable...resources){
        if (resources!=null&&resources.length>0){
            Arrays.stream(resources).forEach(source->{
                if (source!=null){
                    System.out.println("source = " + source);
                    try {
                        source.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            });
        }
    }
//    public static void close(Connection connection){
//        try {
//            connection.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//    }
//
//    public static void close(PreparedStatement preparedStatement,ResultSet resultSet){
//        try {
//            preparedStatement.close();
//            resultSet.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//    }
//
//    public static void close(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
//        try {
//            connection.close();
//            preparedStatement.close();
//            resultSet.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//    }

    /**
     * 5.根据条件查询一条数据,以传入的javaBean类型返回
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static <T> T select(String sql,Class<T> cls,Object... parameters) throws Exception {
       //1.定义所需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        T t=null;
        //2.获取链接
        connection = getConnection();
        //3.获取sql预编译对象
        statement = getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = statement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData md = resultSet.getMetaData();
        System.out.println("根据结果获取了结果集中所有元数据" + md);

//        //System.out.println("count = " + count);
//        //打印列名
//        for (int i = 0; i < count; i++) {
//            String columnName = md.getColumnName(i);
//        }
//        System.out.println();
        //打印结果集
        resultSet.next();
        //while (resultSet.next()) {
            t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
            int count = md.getColumnCount();
            for (int i = 1; i <= count; i++) {
                BeanUtils.setProperty(t,md.getColumnName(i),resultSet.getObject(i));
                //System.out.println(resultSet.getObject(i) + "\t");
            }
            //System.out.println();
        //}
        jdbcTools.closeall(connection,statement,resultSet);
        return t;
    }

    /**
     * 6.根据条件查询多条数据
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static <T> List <T> selectall(String sql, Class<T> cls, Object... parameters) throws Exception {
        //1.定义所需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        ArrayList<T> arrayList = new ArrayList<>();
        T t=null;
        //2.获取链接
        connection = getConnection();
        //3.获取sql预编译对象
        statement = getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = statement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData md = resultSet.getMetaData();
        System.out.println("根据结果获取了结果集中所有元数据" + md);

//        //System.out.println("count = " + count);
//        //打印列名
//        for (int i = 0; i < count; i++) {
//            String columnName = md.getColumnName(i);
//        }
//        System.out.println();
        //打印结果集
        while (resultSet.next()) {
            t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
            int count = md.getColumnCount();
            for (int i = 1; i <= count; i++) {
                BeanUtils.setProperty(t,md.getColumnName(i),resultSet.getObject(i));
                //System.out.println(resultSet.getObject(i) + "\t");
            }
            arrayList.add(t);
            //System.out.println();
        }
        jdbcTools.closeall(connection,statement,resultSet);
        return arrayList;
    }

    /**
     * 7.查询多条记录 以map集合的形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static ArrayList<Map> queryMapList(String sql,Object...parameters) throws Exception {
        //1.定义需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        ArrayList<Map> maps = new ArrayList<>();
        //2.获取链接
        connection = jdbcTools.getConnection();
        //3.获取sql预编译对象
        statement = getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = statement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData md = resultSet.getMetaData();
       // System.out.println("根据结果获取了结果集中所有元数据" + md);
        int count = md.getColumnCount();
        while (resultSet.next()) {
            HashMap<String , Object> map = new HashMap<>();
            for (int i = 1; i <= count; i++) {
                map.put(md.getColumnName(i),resultSet.getObject(i) );
                //System.out.println(resultSet.getObject(i) + "\t");
            }
            maps.add(map);
            //System.out.println();
        }
        jdbcTools.closeall(connection,statement,resultSet);
        return maps;

    }

    /**
     * 8.查询一条记录 以数组的形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Object[] queryArray(String sql,Object...parameters){
        //1.定义需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
       // ArrayList<Map> maps = new ArrayList<>();
        //2.获取链接
        Object[] o = new Object[0];
        try {
            connection = jdbcTools.getConnection();
            //3.获取sql预编译对象
            statement = getPreparedStatement(sql, connection, parameters);
            //4.执行查询，得到结果集
            resultSet = statement.executeQuery();
            //5.反编译对象，从结果集中获取表的元数据
            ResultSetMetaData md = resultSet.getMetaData();
            // System.out.println("根据结果获取了结果集中所有元数据" + md);
            int count = md.getColumnCount();
            o = new Object[count];
            resultSet.next();
            // while (resultSet.next()) {
            // HashMap<String , Object> map = new HashMap<>();
            for (int i = 0; i < count; i++) {
                o[i] = resultSet.getObject(i + 1);
                //System.out.println(resultSet.getObject(i) + "\t");
            }
        } catch (Exception e) {
           return null;
        }
        //maps.add(map);
            //System.out.println();
       // }
        jdbcTools.closeall(connection,statement,resultSet);
        return o;
    }

    /**
     * 9.查询多条记录 以数组集合（集合中数据是数组）的数据状态返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Object[]> queryArrayList(String sql,Object...parameters) throws Exception {
        //1.定义需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Object[]> objects = new ArrayList<>();
        //2.获取链接
        connection = jdbcTools.getConnection();
        //3.获取sql预编译对象
        statement = getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = statement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData md = resultSet.getMetaData();
        // System.out.println("根据结果获取了结果集中所有元数据" + md);
        int count = md.getColumnCount();
        //resultSet.next();
         while (resultSet.next()) {
             Object[] o = new Object[count];//定义长度为count的空数组
        // HashMap<String , Object> map = new HashMap<>();
        for (int i = 0; i < count; i++) {
            o[i] = resultSet.getObject(i + 1);
            //System.out.println(resultSet.getObject(i) + "\t");
        }
        objects.add(o);
        //System.out.println();
         }
        jdbcTools.closeall(connection,statement,resultSet);
        return objects;
    }

    /**
     * 10.查询一条记录，以键值对的形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String ,Object> queryMap(String sql,Object...parameters) throws Exception {
        //1.定义需要的变量
        Connection connection =null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //2.获取链接
        connection = jdbcTools.getConnection();
        //3.获取sql预编译对象
        statement = getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = statement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData md = resultSet.getMetaData();
        // System.out.println("根据结果获取了结果集中所有元数据" + md);
        int count = md.getColumnCount();
        HashMap<String , Object> map = new HashMap<>();
        //resultSet.next();
        while (resultSet.next()) {
            // HashMap<String , Object> map = new HashMap<>();
            for (int i = 1; i <= count; i++) {
                 map.put(md.getColumnName(i),resultSet.getObject(i));
                //System.out.println(resultSet.getObject(i) + "\t");
            }
            //System.out.println();
        }
        jdbcTools.closeall(connection,statement,resultSet);
        return map;
    }

    /**
     * 3.实现删除
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int delete(String sql, Object... parameters) throws Exception {
        Connection connection = jdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 3.实现添加
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int add(String sql, Object... parameters) throws Exception {
        Connection connection = jdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }


}
